package com.easyedu.core.dao;

import com.easyedu.core.entity.dto.DepartmentYear;
import com.easyedu.core.entity.po.Student;
import java.util.List;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;


@Mapper
public interface StudentDao {

     /**
      * 查询某专业学生数量
      *
      * @param instituteId
      * @param departmentId
      * @param grade
      * @return
      */
     @Select("select count(*) from student where institute_id = #{instituteId} AND department_id = #{departmentId} "
             + "and grade = #{grade}")
     Long getStudentNumberByInstitute(Long instituteId, Long departmentId, String grade);

     /**
      * 获取年级范围内根据专业人数排序的专业列表
      *
      * @param startGrade
      * @param endGrade
      * @return
      */
     @Select("select count(1) student_number,department_id,department_name,grade  from student where grade <= "
             + "#{endGrade} and grade >= #{startGrade} group by department_id, department_name, grade order by "
             + "student_number DESC;")
     List<DepartmentYear> getInstituteStudentNumberList(int startGrade, int endGrade);

     /**
      * 根据excel数据插入学生信息
      * @param student
      */
     @Insert({"<script>insert into student set " +
             "student_number = #{studentNumber},institute_id= #{instituteId},institute_name= #{instituteName}," +
             "department_id =#{departmentId}, department_name = #{departmentName},grade = #{grade},administration_class =#{administrationClass}, true_class = #{trueClass},statue = " +
             "#{statue}, name = #{name},sex = #{sex}," +
             "<if test='usedName!=null'>used_name = #{usedName},</if>" +
             "    nation               =#{nation}," +
             "<if test='politics!=null'>politics = #{politics},</if>" +
             "<if test='joinParty!=null'> join_party = #{joinParty},</if>" +
             "<if test='nativePlace!=null'>native_place = #{nativePlace},</if>" +
             "<if test='bornTime!=null'>born_time = #{bornTime},</if>" +
             "    id_number            =#{idNumber}," +
             "    enrollment_date      =#{enrollmentDate}," +
             "    dormitory            =#{dormitory}," +
             "    phone                =#{phone}," +
             "    origin               =#{origin}," +
             "    type                 =#{type}" +
             "<if test='parent1!=null'>,parent1 = #{parent1}</if>" +
             "<if test='parent1IdType!=null'>,parent1_id_type = #{parent1IdType}</if>" +
             "<if test='parent1IdNumber!=null'>,parent1_id_number = #{parent1IdNumber}</if>" +
             "<if test='parent1Phone!=null'>,parent1_phone = #{parent1Phone}</if>" +
             "<if test='parent2!=null'>,parent2 = #{parent2}</if>" +
             "<if test='parent2IdType!=null'>,parent2_id_type=#{parent2IdType}</if>" +
             "<if test='parent2IdNumber!=null'>,parent2_id_number=#{parent2IdNumber}</if>" +
             "<if test='parent2Phone!=null'>,parent2_phone=#{parent2Phone}</if>" +
             "</script>"})
     Integer insertStudentFromExcel(Student student);
}
